package com.webapp.model;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Calendar;


import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

public class ReviewModel {

	
	private static Connection conn = null;
	private static String driver = "com.mysql.jdbc.Driver";
	private static String dbUrl = "jdbc:mysql://mysql1.cs.stonybrook.edu:3306/gdonnelly";
	private static String dbPassword = "106338300";
	private static String dbUser = "gdonnelly";
	
	
	/*
	 * method for entering a new review. this assumes a review has not been entered in at all.
	 */
	public static boolean enterNewReview(String review, double rating, String appEmail, String reviewerEmail){	
		try{
			Statement stmt = connect();
			ResultSet rs;
			
			//reviewer name
			rs = stmt.executeQuery("select * from user where user_email = '" +reviewerEmail+ "'");
			String reviewerName = rs.getString(1);
			
			//review number
			rs = stmt.executeQuery("select * from app_reviews where applicant_email = '" +appEmail+"' and reviewer_email = '" +reviewerEmail+"'");
			int reviewNumber = 1;
			while(rs.next()){
				reviewNumber++;
			}
			
			//date
			Calendar cal = Calendar.getInstance();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String currentDate = sdf.format(cal.getTime());
			
			rs = stmt.executeQuery("insert into app_reviews (applicant_email, reviewer_email, review_number, reviewer_rating, review_date, reviewer_name) values ('" +
										appEmail+ "', '" +reviewerEmail+ "', '" +reviewNumber+ "', '" +rating+ "', '" +currentDate+ "', '" +reviewerName+"')");
			
			//remove row from user_needs_review (I.e. that table states all reviews needed, the user created the review, he no longer needs to review the applicant.
			stmt.executeQuery("REMOVE FROM user_needs_review WHERE (user_email = '" +reviewerEmail+ 
								"' AND applicant_email = '" +appEmail+"'");
			
			//update the number of needed reviews by a user
			rs = stmt.executeQuery("SELECT * FROM user WHERE user_email = '" +reviewerEmail+"'");
			int reviewNum = rs.getInt(6);
			reviewNum--;
			stmt.executeUpdate("UPDATE user SET number_assigned_reviews = '" +reviewNum+"' WHERE user_email = '" +reviewerEmail+"'");
	
			//close database connection
			rs.close();
			stmt.close();
			conn.close();
			
			return true;
		}catch(Exception ex){return false;}
	}
	
	public static Review getExistingReview(String appEmail, String reviewerEmail){
		try{
			Statement stmt = connect();
			ResultSet rs;
			
			rs = stmt.executeQuery("SELECT reviewer_rating, reviewer_review FROM app_reviews WHERE applicant_email = '" +appEmail+ "' AND reviewer_email = '" +reviewerEmail+ "'");
			
			Review review = null;
			if(rs.next()){
				review = new Review(rs.getDouble(1), rs.getString(2));
			}
			
			//close database connection
			rs.close();
			stmt.close();
			conn.close();
			
			return review;
			
		}catch(Exception ex){return null;}
	}
	
	public static boolean updateExistingReview(String appEmail, String reviewerEmail, Review review){
		try{
			Statement stmt = connect();
			ResultSet rs;
			
			rs = stmt.executeQuery("SELECT * FROM app_reviews WHERE applicant_email = '" +appEmail+ "' AND reviewer_email = '" +reviewerEmail+ "'");
			
			//checks if review already exists, and moves the review to previous reviews table, and removes review
			if(rs.next()){
				double rating = rs.getDouble(4);
				int reviewNumber = rs.getInt(3);
				String oReview = rs.getString(5);
				
				//add old review
				stmt.executeQuery("INSERT INTO app_prior_reviews (applicant_email, reviewer_email, reviewer_rating, reviewer_review, review_number) VALUES ('"+
									appEmail+ "', '" +reviewerEmail+ "', '" +rating+ "', '" +oReview+ "', '" +reviewNumber+ "')");
				
				//update new review
				stmt.executeUpdate("UPDATE app_reviews SET reviewer_rating = '" +review.getRating()+ "', reviewer_review = '" +review.getReview()+
									"' WHERE applicant_email = '" +appEmail+ "' AND reviewer_email = '" +reviewerEmail+ "'");
			}
			//old review doesnt exist, cannot update existing review
			else{
				return false;
			}
		}catch(Exception ex){return false;}
		
		
		return true;
	}
	
	public static Statement connect(){
		try{
			Class.forName(driver);
			conn = (Connection) DriverManager.getConnection(dbUrl, dbUser, dbPassword);
			Statement stmt = (Statement) conn.createStatement();
			
			return stmt;
		}catch(Exception ex){return null;}
	}
}
